/*******************************************************************************
 * Copyright (c) 2010 liw.
 * All rights reserved. 
 * 
 * This file is part of VanBus.
 * 
 *     VanBus is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     VanBus is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 * 
 *     You should have received a copy of the GNU General Public License
 *     along with VanBus.  If not, see <http://www.gnu.org/licenses/>.
 * Contributors:
 *     liw - initial API and implementation
 ******************************************************************************/
package org.niclab.vanbus.database;

import java.util.HashSet;
import java.util.List;

import org.niclab.vanbus.model.BusStop;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

/*
 *Currently this STOP table only stores 
 *1.the stops that had once been set favorite(including the ones that had been unset)
 *2.the stops that have latitude and longitude populated.
 * 
 *
 *
 */
public class BusStopDAO extends AbstractDAO{

	
	private static final String LOG_TAG="BusStopDAO";
	
	public static final String DATABASE_TABLE_NAME = ApplicationDataBase.DATABASE_TABLE_STOP;
	
	public static final String[] TABLE_STOP_COLUMNS ={"_id", "STOP_ID", "NAME","LAT","LNG","FAVORITE"};
	public static final String[] TABLE_STOP_COLUMNS_DEFS={" INTEGER PRIMARY KEY ASC AUTOINCREMENT,",
		"VARCHAR(255) UNIQUE NOT NULL,",
		"VARCHAR(255),",
		"NUMERIC,",
		"NUMERIC,",
		"BOOLEAN DEFAULT 0" // 0 is false, 1 is true since sqlite doesn't really have boolean type
		};
	
	public static final String TABLE_STOP_CONSTRAINTS = "";
	
	
	public BusStopDAO(SQLiteOpenHelper openHelper){
		super(openHelper);
	}
	
	public BusStop getBusStop(Cursor cursor){
		BusStop stop = new BusStop();
		stop.setDbId(cursor.getInt(cursor.getColumnIndex("_id")));
		stop.setStopId(cursor.getString(cursor.getColumnIndex("STOP_ID")));
		stop.setStopName(cursor.getString(cursor.getColumnIndex("NAME")));
		stop.setLatitude(cursor.getDouble(cursor.getColumnIndex("LAT")));
		stop.setLongitude(cursor.getDouble(cursor.getColumnIndex("LNG")));
		stop.setFavorite((cursor.getInt(cursor.getColumnIndex("FAVORITE"))==1)?true:false);
		return stop;
	}
	
	public BusStop getBusStop(String stopId){
		
		Cursor cursor = getDB().query(DATABASE_TABLE_NAME, null, "STOP_ID=?", new String[]{stopId}, null, null, null);
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
		
		BusStop stop = new BusStop();
		stop.setDbId(cursor.getInt(cursor.getColumnIndex("_id")));
		stop.setStopId(cursor.getString(cursor.getColumnIndex("STOP_ID")));
		stop.setStopName(cursor.getString(cursor.getColumnIndex("NAME")));
		stop.setLatitude(cursor.getDouble(cursor.getColumnIndex("LAT")));
		stop.setLongitude(cursor.getDouble(cursor.getColumnIndex("LNG")));
		stop.setFavorite((cursor.getInt(cursor.getColumnIndex("FAVORITE"))==1)?true:false);
		
		cursor.close();
		return stop;
	}
	
	public BusStop getBusStop(int dbId){
		Cursor cursor = getDB().query(DATABASE_TABLE_NAME, null, "STOP_ID="+dbId, null, null, null, null);
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
		
		BusStop stop = new BusStop();
		stop.setDbId(cursor.getInt(cursor.getColumnIndex("_id")));
		stop.setStopId(cursor.getString(cursor.getColumnIndex("STOP_ID")));
		stop.setStopName(cursor.getString(cursor.getColumnIndex("NAME")));
		stop.setLatitude(cursor.getDouble(cursor.getColumnIndex("LAT")));
		stop.setLongitude(cursor.getDouble(cursor.getColumnIndex("LNG")));
		stop.setFavorite((cursor.getInt(cursor.getColumnIndex("FAVORITE"))==1)?true:false);
		
		cursor.close();
		return stop;
		
	}
	
	public Cursor getFavoriteStops(){

		Cursor cursor = null;
		cursor = getDB().query(DATABASE_TABLE_NAME, null,"FAVORITE = 1",null, null, null,null);

		return cursor;
	}
	

	public boolean isFavoriteStop(BusStop stop){

		String[] selectArgs = new String[1];
		selectArgs[1]=stop.getStopId();
		String[] columns = {"Favorite"};
		Cursor cursor = null;
		cursor =getDB().query(DATABASE_TABLE_NAME, columns, "STOP_ID=?", selectArgs, null, null, null);
		try{
		if(cursor.moveToFirst()){
			 int fav = cursor.getInt(0);
			
			 return fav==1;
		}
		else
			return false;

		}finally{
			cursor.close();
		}
		
		
	}
	
	public int getStopDbId(BusStop stop){
		if(stop.getDbId()>0)
			return stop.getDbId();
		
		String[] columns = {"_id"};
		
		String selection = "STOP_ID=?";
		String[] selectionArgs = new String[1];
		selectionArgs[0] = stop.getStopId();
		
		Cursor cursor = getDB().query(DATABASE_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		if(cursor.moveToFirst()){
			int dbId = cursor.getInt(0);
			stop.setDbId(dbId);
			cursor.close();
			return dbId;
		}
		
		cursor.close();
		return -1;
		
		
	}
	
	public void checkFavoriteStops(List<BusStop> stops){
		
		StringBuilder whereBuilder = new StringBuilder();
		whereBuilder.append("FAVORITE=1 AND STOP_ID IN (");
		
		for(BusStop stop : stops){
			whereBuilder.append("'");
			whereBuilder.append(stop.getStopId());
			whereBuilder.append("',");
		}
		
		whereBuilder.setCharAt(whereBuilder.length()-1, ')');
		
		SQLiteQueryBuilder qBuilder = new SQLiteQueryBuilder();
		

		qBuilder.appendWhere(whereBuilder.toString());
		qBuilder.setTables(DATABASE_TABLE_NAME);
			
		Cursor cursor = qBuilder.query(getDB(),null,null,null,null, null, null);

		Log.d(LOG_TAG,"checkFavoriteStops: result size: "+ cursor.getCount());

		
		if(cursor.moveToFirst()){

			do{
				int dbId = cursor.getInt(0);
				String stopId=cursor.getString(1);
				for(BusStop stop:stops){
					if(stop.getStopId().equals(stopId)){
						stop.setFavorite(true);
						stop.setDbId(dbId);
					}
				}
					
				
			}while(cursor.moveToNext());
		}
		
		cursor.close();
		
		
	}
	
/*	private boolean isExist(BusStop stop){
		if(stop.getDbId()>0)
			return true;
		
		String[] columns = {"1"};
		
		String selection = "STOP_ID = ?";
		
		String[] selectionArgs = new String[1];
		selectionArgs[1] = stop.getStopId();
		
		Cursor cursor = getDB().query(DATABASE_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		int resultCount = cursor.getCount();
		if(resultCount>0){
			stop.setDbId(cursor.getInt(0));
			return true;
		}
		
		return false;
		
		
	}*/
	
	public void setFavoriteStop(BusStop stop){

		getDB().beginTransaction();
		try{
			stop.setFavorite(true);
			
			ContentValues values = new ContentValues();
			values.put("FAVORITE", true);
			int rowUpdated = getDB().update(DATABASE_TABLE_NAME, values, "STOP_ID=?", new String[]{stop.getStopId()});
			if(rowUpdated ==0) {
					insertBusStop(stop);

			}
			getDB().setTransactionSuccessful();
			
		}finally{
			
			getDB().endTransaction();
		}
		
		
	}
	


	
	public void unsetFavoriteStop(BusStop stop){

		if(stop.getDbId() <=0)
		{
			//this shouldn't happen
			unsetFavoriteStop(stop.getStopId());
		}
		else
			unsetFavoriteStop(stop.getDbId());
	
		stop.setFavorite(false);

	}
	
	
	private void unsetFavoriteStop(int dbId){
		
		
		ContentValues toUpdate = new ContentValues();
		toUpdate.put("FAVORITE", false);
		
		String[] args = new String[1];
		args[0]= String.valueOf(dbId);
	
		getDB().update(DATABASE_TABLE_NAME, toUpdate, "_id = ?",args);
		
		
	}
	
	private void unsetFavoriteStop(String stopId){
	
		ContentValues toUpdate = new ContentValues();
		toUpdate.put("FAVORITE", false);
		
		String[] args = new String[1];
		args[0] = stopId;
		getDB().update(DATABASE_TABLE_NAME,toUpdate, "STOP_ID=?", args);
	
		
	}
	
	public int insertBusStop(BusStop stop){
		
		ContentValues values = new ContentValues();
		values.put("STOP_ID", stop.getStopId());
		values.put("NAME",stop.getStopName() );
		values.put("LAT", stop.getLatitude());
		values.put("LNG",stop.getLongitude());
		values.put("FAVORITE", stop.isFavorite());
		return (int) getDB().insert(DATABASE_TABLE_NAME, "NAME", values);

		
	}
	
	
	public void removeBusStop(BusStop stop){

			getDB().delete(DATABASE_TABLE_NAME, "_id= ? OR STOP_ID= ? ",new String[]{String.valueOf(stop.getDbId()),stop.getStopId()});
	
		
	}
	

	
	
	/**
	 * to save the stop object to database. 
	 * If it already exists in STOP table, database will only be updated if update is true.
	 * If it doesn't exist in database, it will be inserted into the table.
	 * This method should be avoided for updating the favorite status.
	 * @param stop stop object to be saved to database
	 * @param update whether to update the table
	 * 
	 */
	public void saveOrUpdateBusStop(BusStop stop, boolean update){
		
		
		
		getDB().beginTransaction();
		
		try{
			
			int e = insertBusStop(stop);
			//insertion error
			//update the row
			if(e==-1){
				/*
				 * to avoid erasing the coordinations of the stop in database
				 */
				if(stop.getLatitude()==0.0 && stop.getLongitude()==0.0 )
					return;
				
				
				ContentValues values = new ContentValues();
				values.put("LAT", stop.getLatitude());
				values.put("LNG",stop.getLongitude());
				values.put("FAVORITE", stop.isFavorite());
				
				getDB().update(DATABASE_TABLE_NAME, values, "_id=? OR STOP_ID=?", new String[]{String.valueOf(stop.getDbId()),stop.getStopId()});
				
			}
				
			getDB().setTransactionSuccessful();
		}finally{
			getDB().endTransaction();
		}
		
		
	}

	

}
